<?php if (!defined('BASEPATH')) exit('No direct script access allowed');

/* 关于返回结果的json格式建议如下：
data:执行成功返回的数据
status:简单的表示业务执行的状态，如成功success，登录失败，更新失败等
code:业务执行状态的代码（可不设计）
message:表示详细信息
version:版本号 
*/

class TaskTree extends MY_Controller
{

 	//取得网址书签类型的任务列表,调用getallchild
     public function geturlbook()
     {
        //  $this->getallchild(12);
         $this->getallchild_no_root(12);
     }

 	//取得知识库类型的任务列表,调用getallchild
     public function getzsk()
     {
        //  $this->getallchild(10);
         $this->getallchild_no_root(10);
     } 

     //取得所有子孙节点的ID串（包含根节点本身，依靠MYSQL自定义函数（getTaskChildLst））
     private function get_all_child_list($root)
     {
         $this->load->database();
         $query = $this->db->query("select getTaskChildLst('" . $root . "') as childlist;");
         if ($query->num_rows() > 0) {
             $row = $query->row();
             return $row->childlist;
         }else{
             return false;
         }
     }
 
     //取得所有子孙任务列表（使用上述get_all_child_list函数）
     private function getallchild1($parent = 0, $workarea = 0)
     {
         $childlist = $this->get_all_child_list($parent);
 
         if ($this->_login_userid) {
             $this->db->where('uid', $this->_login_userid);
         } else {
             $this->db->where('uid', 5);
         }
         if ($workarea) {
             $this->db->where('areaid', $workarea);
         } else {
         }
         $this->db->where('archive !=', '1'); //默认只取得所有未归档的任务
         $this->db->where(' FIND_IN_SET(id,"' . $childlist . '")>0 ', NULL, FALSE); //从子任务列表中取
         $this->db->order_by('sort desc,parent asc');
         $query = $this->db->get('task');
         if ($num_rows = $query->num_rows() > 0) {
             $result_array['data'] = $query->result_array();
             $result_array['status'] = 'success';
         } else {
             $result_array = array(
                 'status' => 'nofound',
                 'message' => '没有匹配的结果：' . $this->db->last_query()
             );
         }
         $this->array_to_json($result_array);
     }
 
     //不用mysql自定义函数,依赖parents字段
     private function getallchild2($parent = 0){
         $uid = $this->_login_userid ? $this->_login_userid : '5';
         $sql = "select * from task where FIND_IN_SET(".$parent.",parents) AND uid=".$uid.";";
         $this->query_to_json($sql);
     }
 
     // 用mysql8.0递归函数来取得，不依赖parents字段
     private function getallchild3($parent = 0,$noroot=0){
         //注意，根节点是不用带uid条件的，但子节点是需要uid的
         $uid = $this->_login_userid ? $this->_login_userid : '5';
         if($noroot){
            $sql = "with recursive t as ( select * from task where parent = " . $parent . " union all select a.* from task a join t on a.parent = t.id where a.uid=" . $uid ." ) select * from t;";
         }else{
            //  $sql = "with recursive t as ( select * from task where id = " . $parent . " union all select a.* from task a join t on a.parent = t.id where a.uid=" . $uid ." ) select * from t;";
            $sql = "with recursive t as ( select * from task where id = " . $parent . " union all select a.* from task a join t on a.parent = t.id where a.uid=" . $uid ." OR a.pub=1 ) select * from t order by sort desc,parent asc;";
            // $sql = "select task.* from task,team,team_user where task.tid=team.id and team_user.tid=team.id AND team.id=" . $teamid . " AND team_user.uid=" . ($this->_login_userid ? $this->_login_userid : '5') . " order by in_date desc;";
        }
         $this->query_to_json($sql);
     }

      // 用mysql8.0递归函数来取得，不依赖parents字段
     private function __getallchild3_no_root($parent =0){
         //注意，根节点是不用带uid条件的，但子节点是需要uid的
         $uid = $this->_login_userid ? $this->_login_userid : '5';
         $sql = "with recursive t as ( select * from task where parent = " . $parent . " union all select a.* from task a join t on a.parent = t.id where a.uid=" . $uid ." ) select * from t;";
         $this->query_to_json($sql);
     }

     //取得所有子孙任务列表
     public function getallchild($parent =0,$workarea =0 ){
         
        // 依赖mysql自定义函数
         // $this->getallchild1($parent,$workarea);
        //  依赖parents字段
         // $this->getallchild2($parent,$workarea);
        //  依赖mysql8.0递归语句
         $this->getallchild3($parent);
 
     }
     
     public function getallchild_no_root($parent=0){
         $this->getallchild3($parent,1);
     }
  
     //取得所有子孙任务的日程范围（使用上述get_all_child_list函数）
     public function getallchild_start_end($parent = 0, $workarea = 0)
     {
         $childlist = $this->get_all_child_list($parent);
         if ($this->_login_userid) {
             $this->db->where('uid', $this->_login_userid);
         } else {
             $this->db->where('uid', 5);
         }
         if ($workarea) {
             $this->db->where('areaid', $workarea);
         } else {
         }
 
         $this->db->select('min(start_date) as start,max(end_date) as end');
         //		$this->db->where('stage !=', 'finish');//默认只取得所有未完成的任务
         $this->db->where('archive !=', '1'); //默认只取得所有未归档的任务
         $this->db->where(' FIND_IN_SET(id,"' . $childlist . '")>0 ', NULL, FALSE); //从子任务列表中取
         // $this->db->order_by('sort desc,parent asc');
         $query = $this->db->get('task');
         if ($num_rows = $query->num_rows() > 0) {
             $result_array['data'] = $query->result_array();
             $result_array['status'] = 'success';
         } else {
             $result_array = array(
                 'status' => 'nofound',
                 'message' => '没有匹配的结果：' . $this->db->last_query()
             );
         }
         $this->array_to_json($result_array);
     }

	//通过预生成的paents列表，取得所有祖先任务，并以json返回
	public function getparents_by_parentslist($id)
	{
		$this->load->database();
		$sql = "SELECT t1.*,find_in_set(t1.id,t2.parents) as p_order FROM task t1,task t2 WHERE t2.id=" . $id . " AND find_in_set(t1.id,t2.parents) order by p_order desc;";
		$query = $this->db->query($sql);
		if ($num_rows = $query->num_rows() > 0) {
			$result_array['data'] = $query->result_array();
			$result_array['status'] = 'success';
		} else {
			$result_array['status'] = 'nofound';
		}
		//$result_array['data']=array_reverse($parents_array);
		$this->array_to_json($result_array);
	}

	//通过不断循环查询，取得所有祖先任务
	public function getparents($id)
	{
		$this->load->database();
		$parents_array = array();
		$opt = $id;
		do {
			$this->db->where('id', $opt);
			$query = $this->db->get('task');
			//			echo $this->db->last_query();
			if ($query->num_rows() > 0) {
				$row = $query->row_array();
				$result_message = var_export($row, true);
				$parents_array[] = $row;
				$opt = $row['parent'];
				continue;
			} else {
				$opt = NULL;
				break;
			}
		} while ($opt);
		$result_array['message'] = $result_message;
		$result_array['data'] = array_reverse($parents_array);
		$json_string = json_encode($result_array);
		echo $json_string;
	}


}